USE Liberty
GO
---------------------------------------------------------------------------------------------------
--	ServiceHistory
---------------------------------------------------------------------------------------------------
SET NOCOUNT OFF
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO

PRINT 'Creating Table ServiceHistory...'
GO

IF OBJECT_ID( 'ServiceHistory' ) IS NULL
BEGIN
	CREATE TABLE dbo.ServiceHistory
	(OID				bigint		IDENTITY(1, 1)	NOT NULL
	,Version			rowversion
	,PersonnelID		bigint						NOT NULL
	,BranchOfServiceID	bigint						NOT NULL
	,RankID				bigint						NOT NULL
	,StartDate			datetime					NOT NULL
	,EndDate			datetime						NULL
	,UnitAssignment		varchar(200)					NULL
	,TypeOfDischargeID	bigint						NOT NULL
		CONSTRAINT DFLT_ServiceHistory_TypeOfDischargeID DEFAULT (1)	-- Default to "N/A"
	,Active				tinyint						NOT NULL 
		CONSTRAINT DFLT_ServiceHistory_Active DEFAULT (1)
	,UpdatedDate		datetime					NOT NULL
		CONSTRAINT DFLT_ServiceHistory_UpdatedDate DEFAULT GETDATE()
	,CreatedDate		datetime					NOT NULL
		CONSTRAINT DFLT_ServiceHistory_CreatedDate DEFAULT GETDATE()

	,CONSTRAINT PK_ServiceHistory PRIMARY KEY CLUSTERED( OID ) --ON PRIMARY

	,CONSTRAINT FK_ServiceHistory_ServiceMen FOREIGN KEY (PersonnelID)
		REFERENCES ServiceMen(OID)

	,CONSTRAINT FK_ServiceHistory_MilitaryBranch FOREIGN KEY (BranchOfServiceID)
		REFERENCES TypesOfMilitaryBranch(OID)

	,CONSTRAINT FK_ServiceHistory_Rank FOREIGN KEY (RankID)
		REFERENCES MilitaryRanks(OID)

	,CONSTRAINT FK_ServiceHistory_TypesOfDischarge FOREIGN KEY (TypeOfDischargeID)
		REFERENCES TypesOfDischarge(OID)
	)

	PRINT 'Table ServiceHistory has been created successfully.'

END
ELSE 
BEGIN
    PRINT 'Table ServiceHistory already exists.'
END

GO

-- DROP TABLE dbo.ServiceHistory
